package com.qiandw.utils;

import org.apache.commons.codec.Charsets;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.commons.lang3.tuple.Triple;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.nio.charset.Charset;
import java.util.*;
import java.util.function.Function;

/**
 *  Excel 信息标准输入输出
 *  @author coxx
 */
public final class ReadExcelUtil {

    /**
     * Ensures that an object reference passed as a parameter to the calling method is not null.
     *
     * @param reference an object reference
     * @return the non-null reference that was validated
     * @throws NullPointerException if {@code reference} is null
     */
    public static <T> T checkNotNull(T reference) {
        if (reference == null) {
            throw new NullPointerException();
        }
        return reference;
    }

    /**
     * Returns a buffered writer that writes to a file using the given
     * character set.
     *
     * @param file the file to write to
     * @param charset the charset used to encode the output stream; see {@link
     *     Charsets} for helpful predefined constants
     * @return the buffered writer
     */
    public static BufferedWriter newWriter(File file, Charset charset)
            throws FileNotFoundException {
        checkNotNull(file);
        checkNotNull(charset);
        return new BufferedWriter(
                new OutputStreamWriter(new FileOutputStream(file), charset));
    }

    /**
     * 保存数尺信息到指定位置
     * @param stringBuffer String 流
     * @param path 保存路径
     * @param fileName 文件名称 (要求有后缀)
     * @throws IOException IO 操作异常
     */
    private static void saveFileFromBuffer(StringBuilder stringBuffer, String path, String fileName) throws IOException {
        if (stringBuffer.length() != 0){
            File fileTo = new File(path + "/" + fileName);
            if(fileTo.exists()){
                fileTo.delete();
            }
            if (!fileTo.getParentFile().exists()){
                fileTo.getParentFile().mkdirs();
            }
            BufferedWriter writer = newWriter(fileTo, Charset.forName("UTF-8"));
            writer.write(stringBuffer.toString());
            writer.close();
        }
    }

    /**
     * 读取Excel WorkSheet 信息
     * @param path 文件路径
     * @param workSheetName Sheet名
     * @param rows 需要读取指定行 可为空
     * @param colMaps 选取的列和输出模式元组对
     * @param rowStartPattern 指定内容开始行 可为空
     * @param outFormat 输出每行 String 格式
     * @param savePath 保存输出路径
     * @param fileName 保存文件名称 可为空使用 Sheet 作为文件名
     */
    public static void readExcelWorkSheet(String path, String workSheetName, List<Integer> rows, Map<Integer, Function<String,?>> colMaps, String rowStartPattern, String outFormat, String savePath, String fileName) {
        try {
            File file = new File(path);
            InputStream is = new FileInputStream(file);
            Workbook workbook = WorkbookFactory.create(is);
            StringBuilder stringBuffer = new StringBuilder();
            for (Sheet sheet: workbook){
                //获取特定的sheet
                if (workSheetName.equals(sheet.getSheetName())){
                    Integer startRow = 0;
                    //遍历每一行
                    for (Row standRow : sheet){
                        //先判断是否是已选择的rows/
                        if(rows == null || rows.contains(standRow.getRowNum())){
                            String field = getValue(standRow.getCell(0));
                            if(rowStartPattern == null){
                                stringBuffer.append(readRowOperation(standRow, colMaps, outFormat));
                            }else{
                                if(startRow == 0 && rowStartPattern.equals(field)){
                                    startRow = standRow.getRowNum() + 1;
                                    continue;
                                }
                                if (standRow.getRowNum() >= startRow){
                                    stringBuffer.append(readRowOperation(standRow, colMaps, outFormat));
                                }
                            }
                        }
                    }
                }
            }
            is.close();
            //保存文件
            saveFileFromBuffer(stringBuffer, savePath, fileName != null ? fileName : workSheetName + ".txt");
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * 列读取 col 信息
     * @param row 每行单元
     * @param colMaps 选择行处理元组
     * @param lineFormat 每行输入的模式
     * @return 每行输出
     */
    private static String readRowOperation(Row row, Map<Integer, Function<String,?>> colMaps, String lineFormat){
        //这里没有判断是否为 NULL
        int maxCol = colMaps.keySet().stream().max(Comparator.comparingInt(x -> x)).orElse(colMaps.size());
        Set<Integer> needAddNullCollect = new HashSet<>(colMaps.keySet());
        Set<Pair<Integer, String>> columns = new LinkedHashSet<>(maxCol);
        for (Cell cell : row){
            int colAddress = cell.getAddress().getColumn();
            if ((colAddress >= 0 && colAddress <= maxCol)) {
                String cellString = getValue(cell);
                Function<String, ?> translateF = colMaps.get(colAddress);
                //要求数据必须存在
                if(translateF != null){
                    //要求重写toString方法
                    needAddNullCollect.remove(colAddress);
                    columns.add(Pair.of(colAddress,translateF.apply(cellString).toString()));
                }
            }
        }
        //缺失值默认补充
        needAddNullCollect.forEach(x -> columns.add(Pair.of(x, colMaps.get(x).apply("").toString())));
        Object[] pattern = columns.stream().sorted(Comparator.comparingInt(Pair::getLeft)).map(Pair::getRight).toArray();
        return new Formatter().format(lineFormat, pattern).toString();
    }

    /**
     * List 连接成字符串
     * @param join 连接字符串
     * @param listStr 列表
     * @return 已连接字符串
     */
    private static String join(String join,List<String> listStr){
        StringBuilder sb = new StringBuilder();
        for(int i=0,len =listStr.size();i<len;i++){
            if(i==(len-1)){
                sb.append(listStr.get(i));
            }else{
                sb.append(listStr.get(i)).append(join);
            }
        }
        return sb.toString();
    }


    /**
     * 特定Excel SQL 导出 sql 语句
     * @param path 文件路径
     * @param savePath 保存路径
     * @param tableName sql insert 表名
     * @param triples 三元组
     */
    public static void insertSQL(String path, String savePath, String tableName, List<Triple<String, Integer, Function<String, ?>>> triples){
        //生成Format
        String outputFormat = "INSERT INTO %1$s (%2$s) Values (%3$s) \n";
        List<String> fields = new ArrayList<>();
        List<String> values = new ArrayList<>();
        Map<Integer, Function<String, ?>> maps = new LinkedHashMap<>();
        int index = 1;
        String startField = triples.get(0).getLeft();
        for(Triple<String, Integer, Function<String, ?>> triple : triples){
            fields.add(triple.getLeft());
            values.add("%" + index + "$s");
            maps.put(triple.getMiddle(), triple.getRight());
            index++;
        }
        String pattern = String.format(outputFormat, tableName, join(",", fields), join(",", values));
        Date date = Calendar.getInstance().getTime();
        ReadExcelUtil.readExcelWorkSheet(path, "sheet1", null, maps, startField, pattern, savePath, "SQL-OUTPUT-" + date.getTime());
    }

    /**
     * 将其转换为String
     * @param standCell xls 单元
     * @return String 表现形式
     */
    private static String getValue(Cell standCell){
        if(standCell == null){ return ""; }
        CellType cellType = standCell.getCellTypeEnum();
        if (cellType.equals(CellType.BOOLEAN)) {
            return String.valueOf(standCell.getBooleanCellValue());
        } else if (cellType.equals(CellType.NUMERIC)) {
            return String.valueOf(standCell.getNumericCellValue());
        }else if (cellType.equals(CellType.STRING)){
            return String.valueOf(standCell.getStringCellValue());
        }else if (cellType.equals(CellType.FORMULA)){
            return String.valueOf(standCell.getCellFormula());
        }else if (cellType.equals(CellType.ERROR)){
            return String.valueOf(standCell.getErrorCellValue());
        }else{
            return String.valueOf(standCell.getStringCellValue());
        }
    }


    public static void main(String[] args) {
        String startRowPattern = "发卡行名称\n及机构代码";
        String outPutFormat = "this output: %1$s %2$s %3$s -- %4$s \n";
        String filePath = "src/main/resources/fields";
        Map<Integer, Function<String, ?>> maps = new LinkedHashMap<>();
        maps.put(0, x -> x);
        maps.put(1, x -> x);
        maps.put(9, x -> x);
        maps.put(15, x -> x);

        ReadExcelUtil.readExcelWorkSheet("D:\\MySource\\accountDemo\\src\\main\\resources\\最新卡BIN20171122.xls", "sheet1", null, maps, startRowPattern, outPutFormat, filePath, null);

        String startRowPattern2 = "Field1";
        String outPutFormat2 = "INSERT INTO tableName (bankname, path) VALUES (%1$s, %2$s);\n";
        String filePath2 = "src/main/resources/fields";
        Map<Integer, Function<String, ?>> maps2 = new LinkedHashMap<>();
        maps2.put(0, x -> x);
        maps2.put(2, x -> x);
        ReadExcelUtil.readExcelWorkSheet("D:\\MySource\\accountDemo\\src\\main\\resources\\insert语法.xlsx", "SQL", null, maps2, startRowPattern2, outPutFormat2, filePath2, null);

        List<Triple<String, Integer, Function<String, ?>>> triples = new ArrayList<>();
        triples.add(Triple.of("Field1", 0, x -> x));
        triples.add(Triple.of("Field2", 2, x -> String.format("'%1$s'", x)));
        insertSQL("D:\\MySource\\accountDemo\\src\\main\\resources\\insert语法.xlsx", filePath2, "testTable", triples);
    }
}